package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dao.SaleReturnDAO;
import com.model.SaleOrderItem;
import com.model.SaleReturn;
import com.model.SaleReturnItem;
import com.util.DB;

public class SaleReturnDAOImpl implements SaleReturnDAO {
	private Connection conn = DB.con;

	@Override
	public boolean addSaleReturn(SaleReturn saleReturn) {
		PreparedStatement st = null;
		try {
			st = conn
					.prepareStatement("insert into saleReturn(SaleReturnNO,SaleOrder_Id,Reason,Total,CreatDateTime,handler) values(?,?,?,?,now(),?)");
			st.setString(1, saleReturn.getSaleReturnNo());
			st.setInt(2, saleReturn.getSaleOrder_Id());
			st.setString(3, saleReturn.getReason());
			st.setDouble(4, saleReturn.getTotal());
			st.setString(5, saleReturn.getHandler());
			int num = st.executeUpdate();
			if (num > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return false;
	}

	@Override
	public List<?> selectSaleReturn(String key, String type, int page,
			int rows, String sort, String order) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getLastNum() {
		PreparedStatement st = null;
		try {
			int lastNum = 0;
			st = conn
					.prepareStatement("select id from SaleReturn where id = (select max(id) from SaleReturn)");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				lastNum = rs.getInt("id");
			}
			return lastNum;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}

	@Override
	public int getTotal() {
		PreparedStatement st = null;
		int count = 0;
		try {
			st = conn.prepareStatement("select count(*) from SaleReturn");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public int getLikeTotal(String key, String type) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public boolean addSaleReturnItem(SaleReturnItem saleReturnItem) {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		try {
			int saleReturn_id = saleReturnItem.getSaleRetrun_Id();
			int product_id = saleReturnItem.getId();
			int quantity = saleReturnItem.getQuantity();
			double price = saleReturnItem.getPrice();

			st = conn
					.prepareStatement("insert into saleReturnItem(saleReturn_id,product_id,quantity,price) values(?,?,?,?)");
			st.setInt(1, saleReturn_id);
			st.setInt(2, product_id);
			st.setInt(3, quantity);
			st.setDouble(4, price);

			int num = st.executeUpdate();
			if (num > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return false;
	}

	// 根据saleOrderNo查找saleOrderId
	@Override
	public int findBySaleOrderNo(String saleOrderNo) {
		PreparedStatement pstmt = null;
		int id = 0;
		try {
			String sql = "select * from saleOrder where saleOrderNo = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, saleOrderNo);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				id = rs.getInt("id");
			}
			return id;
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			DB.close(pstmt, null);
		}
		return 0;
	}

	// 根据saleOrderId查找saleOrderItem
	@Override
	public List<SaleOrderItem> findBySaleOrder_Id(int saleOrder_id) {
		List<SaleOrderItem> list = new ArrayList<SaleOrderItem>();
		PreparedStatement pstmt = null;
		try {
			String sql = "select * from saleOrderItem where saleOrder_Id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, saleOrder_id);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				SaleOrderItem s = new SaleOrderItem();
				s.setId(rs.getInt("id"));
				s.setSaleOrder_Id(rs.getInt("saleOrder_Id"));
				s.setProduct_Id(rs.getInt("product_Id"));
				s.setQuantity(rs.getInt("quantity"));
				s.setPrice(rs.getDouble("price"));
				list.add(s);
			}
			return list;
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			DB.close(pstmt, null);
		}
		return null;
	}

}
